package com.ywc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.ywc.base.DatabaseConnection;
import com.ywc.vo.Page;
import com.ywc.vo.Test;

public class TestDAO 
{
	private Connection con;
	
	private PreparedStatement ps;
	
	private ResultSet rs;
	
	public List findBypage(Page page)
	{
		List<Test> tests = new ArrayList<Test>();
		
		con = DatabaseConnection.getCon();
		
		String sql = "select * from (select t.*,rownum as myrow from Test t order by id asc) where myrow > ? and myrow <= ?";
		
		try {
			ps = con.prepareStatement(sql);
			
			ps.setInt(1, (page.getCurrentPage()-1)* page.getPerRows());
			
			ps.setInt(2, (page.getCurrentPage()*page.getPerRows()));
			
			rs = ps.executeQuery();
			
			while(rs.next())
			{
				Test test = new Test(rs.getLong("id"),rs.getString("name"),rs.getString("pwd"));
				
				tests.add(test);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally
		{
			DatabaseConnection.closeCon();
		}
		
		
		return tests;
		
	}
	
	public int getTotalRows()
	{
		int mycount = 0;
		
		con = DatabaseConnection.getCon();
		
		String sql = "select count(*) as mycount from Test t";
		
		try {
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			rs.next();
			
			mycount = rs.getInt("mycount");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally
		{
			DatabaseConnection.closeCon();
		}
		
		return mycount;
	}
	
	public static void main(String args[])
	{
		TestDAO tst = new TestDAO();
		
		Page page = new Page();
		
		page.setCurrentPage(1);
		
		page.setPerRows(5);
		
		page.setTotalRows(tst.getTotalRows());
		
		List<Test> ts = tst.findBypage(page);
		
		
		for(Iterator<Test> iterator = ts.iterator();iterator.hasNext();)
		{
			Test test = (Test)iterator.next();
			 
			System.out.println(test.getId()+ "  " + test.getName() + " " + test.getPwd());
		}
		
		
	}
	
	
	
}
